# -*- coding: UTF-8 -*-
import pymysql

# 打开数据库连接
con = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='wy123456',
    db='code_recoad',
    charset='utf8'
)

# 使用cursor()方法获取操作游标
cursor = con.cursor()

# SQL 查询语句
#sql = "SELECT * FROM recode2"
sql = """select recode2.stu_id,stu.stu_name,recode2.time 
        from recode2,stu
        where 
           recode2.stu_id=stu.stu_id
        """

sum = 0  # 统计已打卡人数
try:
    # 执行SQL语句
    cursor.execute(sql)
    # 获取所有记录列表
    results = cursor.fetchall()
    print("       学生打卡记录查询 ")
    print("-----------------------------")
    print("学号        姓名                  打卡时间")
    for it in results:
        for i in range(len(it)):
            print(it[i], end='    ')
        sum += 1
        print("\n")
    print("-----------------------------")
    print("    统计：（" + str(sum) + "）人已打卡")

except:
    print("Error: unable to fecth data")

# 关闭数据库连接
cursor.close()
con.close()
